package com.xu.book.dao.impl;

import com.xu.book.bean.Student;
import com.xu.book.dao.IStudentDao;
import com.xu.sys.utils.MyDbutils;
import com.xu.sys.utils.PageUtils;
import com.xu.sys.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoimpl implements IStudentDao {
    @Override
    public List<Student> listPage(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_student limit ?,?";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select * from t_student where name like '%"+pageUtils.getKey()+"%' limit ?,? ";
        }
        // 计算 分页开始的位置
        int startNo = pageUtils.getStart();
        try {
            List<Student> list = queryRunner.query(sql, new BeanListHandler<Student>(Student.class),startNo,pageUtils.getPageSize());
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public int count(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select count(1) from t_student ";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select count(1) from t_student where name like '%"+pageUtils.getKey()+"%'  ";
        }
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            });
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;

    }

    @Override
    public int save(Student entity) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "insert into t_student(account,name,stuno,age,gender,email,talephone,wechat,address,classid,classname,departname)values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql,entity.getAccount(),entity.getName(),entity.getStuno(),entity.getAge(),entity.getGender(),
                    entity.getEmail(),entity.getTalephone(),entity.getWechat(),entity.getAddress(),entity.getClassid(),entity.getClassname(),entity.getDepartname());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Student findById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_student where id =?";
        try {
            List<Student> list = queryRunner.query(sql,
                    new BeanListHandler<Student>(Student.class),id);
            if (list !=null && list.size() > 0){
                return list.get(0);
            }
            return null;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int updteById(Student entity) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "update t_student set account = ?,name = ?,stuno = ?,age = ?,gender = ?,email = ?," +
                "talephone = ? ,wechat = ?,address = ?,classid = ?,classname = ?,departname = ? where id = ?";
        try {
            return queryRunner.update(sql,entity.getAccount(),entity.getName(),entity.getStuno(),entity.getAge(),entity.getGender(),
                    entity.getEmail(),entity.getTalephone(),entity.getWechat(),entity.getAddress(),entity.getClassid(),entity.getClassname(),entity.getDepartname(),entity.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "delete from t_student where id = ?";
        try {
            return queryRunner.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Student> list() {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_student";

        try {
            List<Student> list = queryRunner.query(sql,
                    new BeanListHandler<Student>(Student.class));
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
